//
//  ViewController.swift
//  SwiftStudyDemoAll
//
//  Created by BINGO on 2019/5/11.
//  Copyright © 2019 mrlong. All rights reserved.
//

import UIKit
//使用FMDB 管理h数据库
class ViewController: UIViewController {
    var db:FMDatabase!
    override func viewDidLoad() {
        super.viewDidLoad()
        self.view.backgroundColor = UIColor.white
        //1 打开数据库
        db = SQLiteManager.shareManger().db
//        let main = TGLinearLayout.init(frame: self.view.frame, orientation: .vert)
//        let btn = UIButton.init(frame: CGRect.init(x: 100, y: 100, width: 100, height: 100))
//        btn.titleLabel?.text = "测试按钮"
//        btn.titleLabel?.textColor = UIColor.cz_random()
//        main.addSubview(btn)
//        self.view.addSubview(main)
        
        //        if db.open() {
        //            print("数据库打开成功")
        //        }else{
        //            print("数据库打开失败\(db.lastErrorMessage())")
        //        }
        //        db.close()
//        //2 创建表
//        createTable()
//        //6 查询
//        findAll()
//        //3 插入数据
//        insert()
//        //6 查询
//        findAll()
//        //4 修改书
//        update()
//        //6 查询
//        findAll()
//        //5 删除
//        delete()
//        //6 查询
//        findAll()
//
//        //7 开事务操作
//        insertTransaction()
//        findAll()
//
//        //对象存入
//        for i in 1..<11{
//            let u = User()
//            u.name = "测试用户\(i)"
//            u.age = 100+i
//            u.sex = 1+i
//            u.save()
//        }
//        findAll()
//        let users = User.rows()
//        for u in users{
//            print(u.id,u.name,u.age,u.sex)
//        }
    }
    func insertTransaction() -> Void {
        if let queue = SQLiteManager.shareManger().dbQueue {
            queue.inTransaction{
                db,
                rollback in
                do {
                    for i in 0..<10 {
                        try db.executeUpdate("insert into User (name,age) values (?,?);", values: ["张三\(i)",i])
                    }
                    print("插入成功")
                } catch {
                    print("插入失败")
                    rollback.pointee = true
                }
            }
        }
    }
    func findAll() -> Void {
        let sql = "select * from User"
        if db.open() {
            if let res = db.executeQuery(sql, withArgumentsIn: []){
                while res.next() {
                    let id = res.int(forColumn: "id")
                    let name = res.string(forColumn: "name")
                    let age = res.int(forColumn: "age")
                    print("\(getCurrTime())----------------------------------------")
                    print(id,name!,age)
                    print("\(getCurrTime())----------------------------------------")
                }
            }else{
                print("查询失败")
            }
        }
        
    }
    func delete() -> Void {
        let sql = "delete from User where id = ?"
        if db.open() {
            if db.executeUpdate(sql, withArgumentsIn: [1]){
                print("删除成功")
            }else{
                print("删除失败")
            }
        }
    }
    func update() -> Void {
        let sql = "update User set name = '张三' where id = 1"
        if db.open() {
            if db.executeUpdate(sql, withArgumentsIn: []){
                print("修改成功")
            }
        }
    }
    func insert() -> Void {
        
        let sql = "insert into User(name,age) values ('zhangsan',102)"
        if db.open() {
            if db.executeUpdate(sql, withArgumentsIn: []){
                print("插入成功")
            }else{
                print("插入失败")
            }
        }
    }
    func createTable() -> Void {
        //建表sql 语句
        let sql = "create table if not exists User ( id integer primary key autoincrement ,name text,age integer);";
        //执行sql
        
        if db.open(){
            if db.executeUpdate(sql, withArgumentsIn: []){
                print("创建表User成功")
            }else {
                print("建表失败")
            }
        }
    }
    func getCurrTime(isTimeStamp:Bool = false) -> String {
        //获取当前时间
        let now = Date()
        if !isTimeStamp {
            // 创建一个日期格式器
            let dformatter = DateFormatter()
            dformatter.dateFormat = "yyyy年MM月dd日 HH:mm:ss"
            return dformatter.string(from: now)
        }else {
            
            //当前时间的时间戳
            let timeInterval:TimeInterval = now.timeIntervalSince1970
            let timeStamp = Int(timeInterval)
            return "\(timeStamp)"
        }
    }
}

